package com.edianping.store;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.edianping.bean.ReviewItemBean;
import com.edianping.util.LogCode;

public class ReviewDBStore extends AbstractDBStore {
	protected static Logger log = Logger.getLogger(ReviewDBStore.class);
	
	public static List<ReviewItemBean> GetReviews( int startIndex, int perpage){
		List<ReviewItemBean> result = new ArrayList<ReviewItemBean>();
		
		Connection conn = null;
		Statement stmt = null;
		
		
		String sql = "select * "
					+ "from review r"
					+ " order by review_date desc"
					+ " limit "+startIndex+", " + perpage;
		
		try {
			conn = pool.getConnection();
			stmt = conn.createStatement();
		
			ResultSet rs = stmt.executeQuery( sql );
		
			ReviewItemBean b;
			while( rs.next() ){
				b = new ReviewItemBean( rs.getString( "id" ) );
				b.setProductidlist( rs.getString( "product_id_list" ) );
				b.setShopid( rs.getString( "shop_id" ) );
				b.setRate( rs.getInt( "rate" ) );
				b.setReality( rs.getInt( "reality" ) );
				b.setService( rs.getInt( "service" ) );
				b.setVelocity( rs.getInt( "velocity" ) );
				b.setScore( rs.getInt( "score" ) );
				b.setEgg_count( rs.getInt( "egg_count" ) );
				b.setFlower_count( rs.getInt( "flower_count" ) );
				b.setTags( rs.getString( "tag" ) );
				b.setPaymethod( rs.getString( "paymethod" ) );
				b.setFeature( rs.getString( "feature" ) );
				b.setCommentLength( rs.getString("content").length());
				b.setPicCount( rs.getInt("pic_count") );
				b.setLoginCount( UserLoginEventDBStore.GetUserLoginCountById( rs.getInt("user_id"), 30) );
				result.add( b );
			}
			
			rs.close();
			stmt.close();
			
		} catch (Exception e) {
			log.error("[[" + LogCode.E_DB_SQL_ERROR + "]], SQL:[" + sql+ "]");
			log.debug(e);
			if (log.isDebugEnabled())
				log.debug(e);
		} finally {
			if (conn != null)
				pool.checkin(conn);
		}
		
		return result;
	}
	
	public static List<ReviewItemBean> GetReviewByUid( int userId ){
		List<ReviewItemBean> result = new ArrayList<ReviewItemBean>();
		
		Connection conn = null;
		Statement stmt = null;
		
		
		String sql = "select * "
					+ "from review r"
					+ " where user_id="+userId
					+ " order by review_date desc";
		
		try {
			conn = pool.getConnection();
			stmt = conn.createStatement();
		
			ResultSet rs = stmt.executeQuery( sql );
		
			ReviewItemBean b;
			while( rs.next() ){
				b = new ReviewItemBean( rs.getString( "id" ) );
				b.setProductidlist( rs.getString( "product_id_list" ) );
				b.setShopid( rs.getString( "shop_id" ) );
				b.setRate( rs.getInt( "rate" ) );
				b.setReality( rs.getInt( "reality" ) );
				b.setService( rs.getInt( "service" ) );
				b.setVelocity( rs.getInt( "velocity" ) );
				b.setScore( rs.getInt( "score" ) );
				b.setEgg_count( rs.getInt( "egg_count" ) );
				b.setFlower_count( rs.getInt( "flower_count" ) );
				b.setTags( rs.getString( "tag" ) );
				b.setPaymethod( rs.getString( "paymethod" ) );
				b.setFeature( rs.getString( "feature" ) );
				b.setCommentLength( rs.getString("content").length());
				b.setPicCount( rs.getInt("pic_count") );
				result.add( b );
			}
			
			rs.close();
			stmt.close();
			
		} catch (Exception e) {
			log.error("[[" + LogCode.E_DB_SQL_ERROR + "]], SQL:[" + sql+ "]");
			log.debug(e);
			if (log.isDebugEnabled())
				log.debug(e);
		} finally {
			if (conn != null)
				pool.checkin(conn);
		}
		
		return result;
	}
	
	public static List<ReviewItemBean> GetReviewsByShopId(int shopid) {
		List<ReviewItemBean> result = new ArrayList<ReviewItemBean>();

		Connection conn = null;
		Statement stmt = null;
		String sql = "select *, ( select contribution from user_statistic where user_id=r.user_id) as reputation "
				+ "from review r where shop_id="
				+ shopid
				+ " order by review_date desc";

		try {
			conn = pool.getConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql);

			ReviewItemBean b;
			int count = 1;
			while (rs.next()) {
				b = new ReviewItemBean(rs.getString("id"));
				b.setWeight(count);
				b.setProductidlist(rs.getString("product_id_list"));
				b.setShopid(rs.getString("shop_id"));
				b.setRate(rs.getInt("rate"));
				b.setReality(rs.getInt("reality"));
				b.setService(rs.getInt("service"));
				b.setVelocity(rs.getInt("velocity"));
				b.setScore(rs.getInt("score"));
				b.setEgg_count(rs.getInt("egg_count"));
				b.setFlower_count(rs.getInt("flower_count"));
				b.setUserReputation(rs.getInt("reputation"));
				b.setCommentLength( rs.getString("content").length());
				b.setPicCount( rs.getInt("pic_count") );
				result.add(b);

				count++;
			}

			rs.close();
			stmt.close();

		} catch (Exception e) {
			log.error("[[" + LogCode.E_DB_SQL_ERROR + "]], SQL:[" + sql + "]");
			log.debug(e);
			if (log.isDebugEnabled())
				log.debug(e);
		} finally {
			if (conn != null)
				pool.checkin(conn);
		}

		return result;
	}
}
